/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ticketmaster.dao;

import com.sun.rowset.CachedRowSetImpl;
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.RowSet;
import javax.sql.rowset.CachedRowSet;
import ticketmaster.util.Converter;

/**
 *
 * @author Wei Liu
 */
public class BaseEntityDAO
{

    /**
     * query in database to get a result set
     * @param sql the query sql statement
     * @return a rowset containing the results that match the supplied criteria
     */
    protected RowSet query(String sql)
    {
        Connection conn = null;
        try
        {
            conn = DAOFactory.createConnection();
        }
        catch (SQLException ex)
        {
            Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        catch (IOException ex)
        {
            Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        catch (ClassNotFoundException ex)
        {
            Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        ResultSet rs;
        CachedRowSet crs;

        try
        {
            Statement stat = conn.createStatement();
            rs = stat.executeQuery(sql);
            crs = new CachedRowSetImpl();
            crs.populate(rs);
        }
        catch (Exception ex)
        {
            return null;
        }
        finally
        {
            try
            {
                conn.close();
            }
            catch (SQLException ex)
            {
                Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return crs;
    }

    /**
     * execute the sql statement to add/update/delete data
     * @param sql the sql to be executed
     * @param type the type of the operation, create, update, or delete
     * @return generated primary key(create) or rownumber(update,delete) or a 0 on error
     */
    protected int execute(String sql, DataOperationType type)
    {
        Connection conn = null;
        try
        {
            conn = DAOFactory.createConnection();
        }
        catch (SQLException ex)
        {
            Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        catch (IOException ex)
        {
            Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        catch (ClassNotFoundException ex)
        {
            Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        int result = 0;
        try
        {
            Statement stat = conn.createStatement();
            ResultSet rs = null;
            if (type == DataOperationType.Create)
            {
                stat.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
                rs = stat.getGeneratedKeys();
                if (rs.next())
                {
                    result = rs.getInt(1);
                }
            }
            else
            {
                result = stat.executeUpdate(sql);
            }
        }
        catch (Exception ex)
        {
            return 0;
        }
        finally
        {
            try
            {
                conn.close();
            }
            catch (SQLException ex)
            {
                Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        if(result == 0)
            result = 1;

        return result;
    }

    /**
     * Insert object into database
     * @param object the object to be inserted
     * @return generated primary key or a 0 on error
     */
    public int save(Object object)
    {
        Class cla = object.getClass();
        Field[] fields = cla.getDeclaredFields();
        StringBuilder sb = new StringBuilder();
        String className = cla.getName().replaceAll("ticketmaster.dataobject.", "");

        sb.append("insert into `");
        sb.append(className);
        sb.append("` (");

        for (Field field : fields)
        {
            if (field.getName().equals(className.toLowerCase() + "ID"))
            {
                if (!"customerID".equals(className.toLowerCase() + "ID"))
                {
                    continue;
                }
            }

            field.setAccessible(true);
            try
            {
                Object obj = field.get(object);
                field.setAccessible(false);
                //if ("int".equals(field.getType().toString())
                //|| "float".equals(field.getType().toString())
                //|| "double".equals(field.getType().toString()))
                //{
                //if (Integer.parseInt(obj.toString()) != 0)
                //{
                //sb.append(field.getName());
                //sb.append(", ");
                //}
                //else
                //{
                //continue;
                //}
                //}
                //else if (obj != null)
                if (obj != null)
                {
                    sb.append(field.getName());
                    sb.append(", ");
                }
            }
            catch (IllegalArgumentException ex)
            {
                Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            catch (IllegalAccessException ex)
            {
                Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        sb.replace(sb.lastIndexOf(", "), sb.lastIndexOf(", ") + 1, ")");
        sb.append(" values (");

        for (Field field : fields)
        {
            if (field.getName().equals(className.toLowerCase() + "ID"))
            {
                if (!"customerID".equals(className.toLowerCase() + "ID"))
                {
                    continue;
                }
            }

            field.setAccessible(true);
            try
            {
                Object obj = field.get(object);
                field.setAccessible(false);
                String parameter = null;
                if ("int".equals(field.getType().toString())
                    || "float".equals(field.getType().toString())
                    || "double".equals(field.getType().toString())
                    || "boolean".equals(field.getType().toString()))
                {
                    //if (Integer.parseInt(obj.toString()) != 0)
                    //{
                    if ("boolean".equals(field.getType().toString()))
                    {
                        parameter = Converter.BooleanToIntString(obj.toString());
                    }
                    else
                    {
                        parameter = obj.toString();
                    }
                    //}
                    //else
                    //{
                    //continue;
                    //}
                }
                else
                {
                    if (obj != null)
                    {
                        parameter = "\'" + obj.toString() + "\'";
                    }
                    else
                    {
                        continue;
                    }
                }

                sb.append(parameter);
                sb.append(", ");
            }
            catch (IllegalArgumentException ex)
            {
                Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            catch (IllegalAccessException ex)
            {
                Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        sb.replace(sb.lastIndexOf(", "), sb.lastIndexOf(", ") + 1, ")");

        return execute(sb.toString(), DataOperationType.Create);
    }

    /**
     * Update the object in database
     * @param object the object to be updated
     * @return true on success, false on failure or error
     */
    public boolean update(Object object)
    {
        Class cla = object.getClass();
        Field[] fields = cla.getDeclaredFields();
        StringBuilder sb = new StringBuilder();
        String className = cla.getName().replaceAll("ticketmaster.dataobject.", "");

        sb.append("update `");
        sb.append(className);
        sb.append("` set ");

        for (Field field : fields)
        {
            if (field.getName().equals(className.toLowerCase() + "ID"))
            {
                continue;
            }
            //--------------- Added by Wei Liu 04/12
            field.setAccessible(true);
            try
            {
                Object o = field.get(object);
                if(o == null)
                continue;
            }
            catch (IllegalArgumentException ex)
            {
                Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            catch (IllegalAccessException ex)
            {
                Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            field.setAccessible(false);
            //--------------- Added by Wei Liu 04/12
            sb.append(field.getName());
            sb.append(" = ");
            field.setAccessible(true);
            try
            {
                Object obj = field.get(object);
                field.setAccessible(false);
                String parameter = null;
                if ("int".equals(field.getType().toString()))
                {
                    if (Integer.parseInt(obj.toString()) != 0)
                    {
                        parameter = obj.toString();
                    }
                    else
                    {
                        parameter = "0";
                    }
                }
                else if ("float".equals(field.getType().toString())
                         || "double".equals(field.getType().toString()))
                {
                    if (Double.parseDouble(obj.toString()) != 0)
                    {
                        parameter = obj.toString();
                    }
                    else
                    {
                        parameter = "0";
                    }
                }
                //--------------- Added by Wei Liu 04/12
                else if ("boolean".equals(field.getType().toString()))
                {
                    parameter = Converter.BooleanToIntString(obj.toString());
                }
                //---------------
                else
                {
                    if (obj != null)
                    {
                        parameter = "\'" + obj.toString() + "\'";
                    }
                    else
                    {
                        parameter = "";
                    }
                }

                sb.append(parameter);
                sb.append(", ");
            }
            catch (IllegalArgumentException ex)
            {
                Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            catch (IllegalAccessException ex)
            {
                Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        sb.replace(sb.lastIndexOf(", "), sb.lastIndexOf(", ") + 1, "");
        sb.append(" where ");

        RowSet crs = null;
        try
        {
            crs = getPrimaryKeys(className);
        }
        catch (SQLException ex)
        {
            Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        catch (IOException ex)
        {
            Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        catch (ClassNotFoundException ex)
        {
            Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        {
            while (crs.next())
            {
                String fieldName = crs.getObject(4).toString();
                Field field = null;
                try
                {
                    field = cla.getDeclaredField(fieldName);
                }
                catch (NoSuchFieldException ex)
                {
                    Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
                catch (SecurityException ex)
                {
                    Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
                sb.append(fieldName);
                sb.append("=");
                field.setAccessible(true);
                try
                {
                    Object obj = field.get(object);
                    field.setAccessible(false);
                    String parameter = null;
                    if ("int".equals(field.getType().toString()) || "float".equals(field.getType().toString()) || "double".equals(field.getType().toString()))
                    {
                        if (Integer.parseInt(obj.toString()) != 0)
                        {
                            parameter = obj.toString();
                        }
                        else
                        {
                            parameter = "0";
                        }
                    }
                    else
                    {
                        if (obj != null)
                        {
                            parameter = "\'" + obj.toString() + "\'";
                        }
                        else
                        {
                            parameter = "";
                        }
                    }
                    sb.append(parameter);
                    sb.append(" and ");
                }
                catch (IllegalArgumentException ex)
                {
                    Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
                catch (IllegalAccessException ex)
                {
                    Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        catch (SQLException ex)
        {
            Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        sb.replace(sb.lastIndexOf("and "), sb.lastIndexOf("and ") + 4, "");

        if (execute(sb.toString(), DataOperationType.Update) == 0)
        {
            return false;
        }
        else
        {
            return true;
        }
    }

    /**
     * Delete the object from database
     * @param object the object to be deleted
     * @return true on success, false on failure or error
     */
    public boolean delete(Object object)
    {
        Class cla = object.getClass();
        StringBuilder sb = new StringBuilder();
        String className = cla.getName().replaceAll("ticketmaster.dataobject.", "");

        sb.append("delete from `");
        sb.append(className);
        sb.append("` where ");

        RowSet crs = null;
        try
        {
            crs = getPrimaryKeys(className);
        }
        catch (SQLException ex)
        {
            Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        catch (IOException ex)
        {
            Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        catch (ClassNotFoundException ex)
        {
            Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        try
        {
            while (crs.next())
            {
                String fieldName = crs.getObject(4).toString();
                Field field = null;
                try
                {
                    field = cla.getDeclaredField(fieldName);
                }
                catch (NoSuchFieldException ex)
                {
                    Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
                catch (SecurityException ex)
                {
                    Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
                sb.append(fieldName);
                sb.append("=");
                field.setAccessible(true);
                try
                {
                    Object obj = field.get(object);
                    field.setAccessible(false);
                    String parameter = null;
                    if ("int".equals(field.getType().toString()) || "float".equals(field.getType().toString()) || "double".equals(field.getType().toString()))
                    {
                        if (Integer.parseInt(obj.toString()) != 0)
                        {
                            parameter = obj.toString();
                        }
                        else
                        {
                            parameter = "0";
                        }
                    }
                    else
                    {
                        if (obj != null)
                        {
                            parameter = "\'" + obj.toString() + "\'";
                        }
                        else
                        {
                            parameter = "";
                        }
                    }
                    sb.append(parameter);
                    sb.append(" and ");
                }
                catch (IllegalArgumentException ex)
                {
                    Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
                catch (IllegalAccessException ex)
                {
                    Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        catch (SQLException ex)
        {
            Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        sb.replace(sb.lastIndexOf("and "), sb.lastIndexOf("and ") + 4, "");

        if (execute(sb.toString(), DataOperationType.Delete) == 0)
        {
            return false;
        }
        else
        {
            return true;
        }
    }

    /**
     * Generic sql builder, may not be used any more
     * @param type data operation type
     * @param object the mapped object
     * @return sql statement
     */
    protected String buildSQL(DataOperationType type, Object object)
    {
        Class cla = object.getClass();
        Field[] fields = cla.getDeclaredFields();
        StringBuilder sb = new StringBuilder();
        String className = cla.getName().replaceAll("ticketmaster.dataobject.", "");

        if (type == DataOperationType.Create)
        {
            sb.append("insert into `");
            sb.append(className);
            sb.append("` (");

            for (Field field : fields)
            {
                if (field.getName().equals(className.toLowerCase() + "ID"))
                {
                    continue;
                }

                field.setAccessible(true);
                try
                {
                    Object obj = field.get(object);
                    field.setAccessible(false);
                    if ("int".equals(field.getType().toString())
                        || "float".equals(field.getType().toString())
                        || "double".equals(field.getType().toString()))
                    {
                        if (Integer.parseInt(obj.toString()) != 0)
                        {
                            sb.append(field.getName());
                            sb.append(", ");
                        }
                        else
                        {
                            continue;
                        }
                    }
                    else if (obj != null)
                    {
                        sb.append(field.getName());
                        sb.append(", ");
                    }
                }
                catch (IllegalArgumentException ex)
                {
                    Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
                catch (IllegalAccessException ex)
                {
                    Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
            }

            sb.replace(sb.lastIndexOf(", "), sb.lastIndexOf(", ") + 1, ")");
            sb.append(" values (");

            for (Field field : fields)
            {
                field.setAccessible(true);
                try
                {
                    Object obj = field.get(object);
                    field.setAccessible(false);
                    String parameter = null;
                    if ("int".equals(field.getType().toString())
                        || "float".equals(field.getType().toString())
                        || "double".equals(field.getType().toString()))
                    {
                        if (Integer.parseInt(obj.toString()) != 0)
                        {
                            parameter = obj.toString();
                        }
                        else
                        {
                            continue;
                        }
                    }
                    else
                    {
                        if (obj != null)
                        {
                            parameter = "\'" + obj.toString() + "\'";
                        }
                        else
                        {
                            continue;
                        }
                    }

                    sb.append(parameter);
                    sb.append(", ");
                }
                catch (IllegalArgumentException ex)
                {
                    Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
                catch (IllegalAccessException ex)
                {
                    Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
            }

            sb.replace(sb.lastIndexOf(", "), sb.lastIndexOf(", ") + 1, ")");
        }
        else if (type == DataOperationType.Delete)
        {
            sb.append("delete from `");
            sb.append(className);
            sb.append("` where ");

            for (Field field : fields)
            {
                field.setAccessible(true);
                try
                {
                    Object obj = field.get(object);
                    field.setAccessible(false);
                    String parameter = null;
                    if ("int".equals(field.getType().toString())
                        || "float".equals(field.getType().toString())
                        || "double".equals(field.getType().toString()))
                    {
                        if (Integer.parseInt(obj.toString()) != 0)
                        {
                            parameter = obj.toString();
                        }
                        else
                        {
                            continue;
                        }
                    }
                    else
                    {
                        if (obj != null)
                        {
                            parameter = "\'" + obj.toString() + "\'";
                        }
                        else
                        {
                            continue;
                        }
                    }

                    sb.append(field.getName());
                    sb.append(" = ");
                    sb.append(parameter);
                    sb.append(" and ");
                }
                catch (IllegalArgumentException ex)
                {
                    Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
                catch (IllegalAccessException ex)
                {
                    Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
            }

            sb.replace(sb.lastIndexOf("and "), sb.lastIndexOf("and ") + 4, "");

        }
        else if (type == DataOperationType.Read)
        {
            sb.append("select * from ticket_master.`");
            sb.append(className);
            sb.append("`");

            boolean hasWhere = false;

            for (Field field : fields)
            {
                field.setAccessible(true);
                try
                {
                    Object obj = field.get(object);
                    field.setAccessible(false);
                    if ("int".equals(field.getType().toString())
                        || "float".equals(field.getType().toString())
                        || "double".equals(field.getType().toString()))
                    {
                        if (Integer.parseInt(obj.toString()) != 0)
                        {
                            hasWhere = true;
                            break;
                        }
                        else
                        {
                            continue;
                        }
                    }
                    else if (obj != null)
                    {
                        hasWhere = true;
                        break;
                    }
                }
                catch (IllegalArgumentException ex)
                {
                    Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
                catch (IllegalAccessException ex)
                {
                    Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
            }

            if (!hasWhere)
            {
                return sb.toString();
            }

            sb.append(" where ");

            for (Field field : fields)
            {
                field.setAccessible(true);
                try
                {
                    Object obj = field.get(object);
                    field.setAccessible(false);
                    String parameter = null;
                    if ("int".equals(field.getType().toString())
                        || "float".equals(field.getType().toString())
                        || "double".equals(field.getType().toString()))
                    {
                        if (Integer.parseInt(obj.toString()) != 0)
                        {
                            parameter = obj.toString();
                        }
                        else
                        {
                            continue;
                        }
                    }
                    else
                    {
                        if (obj != null)
                        {
                            parameter = "\'" + obj.toString() + "\'";
                        }
                        else
                        {
                            continue;
                        }
                    }

                    sb.append(field.getName());
                    sb.append(" = ");
                    sb.append(parameter);
                    sb.append(" and ");
                }
                catch (IllegalArgumentException ex)
                {
                    Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
                catch (IllegalAccessException ex)
                {
                    Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
            }

            sb.replace(sb.lastIndexOf("and "), sb.lastIndexOf("and ") + 4, "");
        }
        else if (type == DataOperationType.Update)
        {
            sb.append("update `");
            sb.append(className);
            sb.append("` set ");

            for (Field field : fields)
            {
                if (field.getName().equals(className.toLowerCase() + "ID"))
                {
                    continue;
                }
                sb.append(field.getName());
                sb.append(" = ");
                field.setAccessible(true);
                try
                {
                    Object obj = field.get(object);
                    field.setAccessible(false);
                    String parameter = null;
                    if ("int".equals(field.getType().toString())
                        || "float".equals(field.getType().toString())
                        || "double".equals(field.getType().toString()))
                    {
                        if (Integer.parseInt(obj.toString()) != 0)
                        {
                            parameter = obj.toString();
                        }
                        else
                        {
                            parameter = "0";
                        }
                    }
                    else
                    {
                        if (obj != null)
                        {
                            parameter = "\'" + obj.toString() + "\'";
                        }
                        else
                        {
                            parameter = "";
                        }
                    }

                    sb.append(parameter);
                    sb.append(", ");
                }
                catch (IllegalArgumentException ex)
                {
                    Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
                catch (IllegalAccessException ex)
                {
                    Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
            }

            sb.replace(sb.lastIndexOf(", "), sb.lastIndexOf(", ") + 1, "");
            sb.append(" where ");

            RowSet crs = null;
            try
            {
                crs = getPrimaryKeys(className);
            }
            catch (SQLException ex)
            {
                Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            catch (IOException ex)
            {
                Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            catch (ClassNotFoundException ex)
            {
                Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            try
            {
                while (crs.next())
                {
                    String fieldName = crs.getObject(4).toString();
                    Field field = null;
                    try
                    {
                        field = cla.getDeclaredField(fieldName);
                    }
                    catch (NoSuchFieldException ex)
                    {
                        Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
                    }
                    catch (SecurityException ex)
                    {
                        Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
                    }
                    sb.append(fieldName);
                    sb.append("=");
                    field.setAccessible(true);
                    try
                    {
                        Object obj = field.get(object);
                        field.setAccessible(false);
                        String parameter = null;
                        if ("int".equals(field.getType().toString()) || "float".equals(field.getType().toString()) || "double".equals(field.getType().toString()))
                        {
                            if (Integer.parseInt(obj.toString()) != 0)
                            {
                                parameter = obj.toString();
                            }
                            else
                            {
                                parameter = "0";
                            }
                        }
                        else
                        {
                            if (obj != null)
                            {
                                parameter = "\'" + obj.toString() + "\'";
                            }
                            else
                            {
                                parameter = "";
                            }
                        }
                        sb.append(parameter);
                        sb.append(" and ");
                    }
                    catch (IllegalArgumentException ex)
                    {
                        Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
                    }
                    catch (IllegalAccessException ex)
                    {
                        Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
                    }
                }
            }
            catch (SQLException ex)
            {
                Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
            }

            sb.replace(sb.lastIndexOf("and "), sb.lastIndexOf("and ") + 4, "");
        }

        return sb.toString();
    }

    /**
     * Generate simple query statement
     * @param object the mapped object
     * @return sql query statement
     */
    protected String buildQuerySQL(Object object)
    {
        Class cla = object.getClass();
        Field[] fields = cla.getDeclaredFields();
        StringBuilder sb = new StringBuilder();
        String className = cla.getName().replaceAll("ticketmaster.dataobject.", "");

        sb.append("select * from ticket_master.`");
        sb.append(className);
        sb.append("`");

        boolean hasWhere = false;

        for (Field field : fields)
        {
            field.setAccessible(true);
            try
            {
                Object obj = field.get(object);
                field.setAccessible(false);
                if ("int".equals(field.getType().toString())
                    || "float".equals(field.getType().toString())
                    || "double".equals(field.getType().toString()))
                {
                    if (Integer.parseInt(obj.toString()) != 0)
                    {
                        hasWhere = true;
                        break;
                    }
                    else
                    {
                        continue;
                    }
                }
                else if (obj != null)
                {
                    hasWhere = true;
                    break;
                }
            }
            catch (IllegalArgumentException ex)
            {
                Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            catch (IllegalAccessException ex)
            {
                Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        if (!hasWhere)
        {
            return sb.toString();
        }

        sb.append(" where ");

        for (Field field : fields)
        {
            field.setAccessible(true);
            try
            {
                Object obj = field.get(object);
                field.setAccessible(false);
                String parameter = null;
                if ("int".equals(field.getType().toString())
                    || "float".equals(field.getType().toString())
                    || "double".equals(field.getType().toString()))
                {
                    if (Integer.parseInt(obj.toString()) != 0)
                    {
                        parameter = obj.toString();
                    }
                    else
                    {
                        continue;
                    }
                }
                else if ("boolean".equals(field.getType().toString()))
                {
                    parameter = String.valueOf(Converter.BooleanToInt(obj.toString()));
                }
                else
                {
                    if (obj != null)
                    {
                        parameter = "\'" + obj.toString().replaceAll("'", "''") + "\'";
                    }
                    else
                    {
                        continue;
                    }
                }

                sb.append(field.getName());
                sb.append(" = ");
                sb.append(parameter);
                sb.append(" and ");
            }
            catch (IllegalArgumentException ex)
            {
                Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            catch (IllegalAccessException ex)
            {
                Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        sb.replace(sb.lastIndexOf("and "), sb.lastIndexOf("and ") + 4, "");

        return sb.toString();
    }

    protected RowSet getPrimaryKeys(String className) throws SQLException, IOException, ClassNotFoundException
    {
        Connection conn = DAOFactory.createConnection();
        DatabaseMetaData metaData = conn.getMetaData();

        ResultSet rs = metaData.getPrimaryKeys(null, null, className);
        CachedRowSet crs = new CachedRowSetImpl();
        crs.populate(rs);
        conn.close();

        return crs;
    }

    protected Object getObject(RowSet rs, Object object)
    {
        Class cla = object.getClass();

        Field[] fields = cla.getDeclaredFields();

        for (Field field : fields)
        {
            field.setAccessible(true);

            try
            {
                field.set(object, rs.getObject(field.getName()));
            }
            catch (IllegalArgumentException ex)
            {
                Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            catch (IllegalAccessException ex)
            {
                Logger.getLogger(BaseEntityDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            catch (Exception e)
            {
                return null;
            }

            field.setAccessible(false);
        }

        return object;
    }
}
